<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Transaction
 *
 * @author Massri
 */
class Application_Model_Transaction {
    //put your code here
    
    private $_db;
    
    function __construct() {
        $registry = Zend_Registry::getInstance();
        $this->_db = $registry->database;
    }

    function addBonusReferensi($sponsorId){

        /*
         * GET Account
         */
        $sql = "select acc_num from accounts where id_reff1 = $sponsorId";
        $seq = $this->_db->fetchRow($sql);
        $accNum = $seq['acc_num'];


        /*
         * GET Bonus Attribut
         */
        $sql = "select amount_per_unit, descr from bonus_type where bonus_type_id = 1";
        $seq = $this->_db->fetchRow($sql);
        $amount = $seq['amount_per_unit'];
        $descr  = $seq['descr'];


        /*
         * Insert Trans Header
         */
        $data = array(
            'trans_type_id' => 1,
            'trans_dtm' => new Zend_Db_Expr('NOW()'),
            'user_ref'  => $sponsorId,
            'trans_status' => 0
        );

        $this->_db->insert('trans_header',$data);
        $idH = $this->_db->lastInsertId();

        
        /*
         * Insert Trans detail
         */
        $data = array(
            'trans_hid' => $idH,
            'acc_num1' => $accNum,
            'amt1'  => $amount,
            'descr' => $descr
        );

        $this->_db->insert('trans_detail',$data);
        $idD = $this->_db->lastInsertId();


        /*
         * Update Accounts
         */
        $sql = "update accounts set balance = balance+$amount where id_reff1 = ".$sponsorId;
        $this->_db->query($sql);


        $result = array("trans_hid" => $idH);

        return $result;
    }

    function withdraw($memberId, $accId, $tarik, $bank){

        $this->_db->beginTransaction();

        $result = array();

        try {
            /*
             * GET Account
             */
            $sql = "select acc_num from accounts where acc_id = $accId";
            $seq = $this->_db->fetchRow($sql);
            $accNum = $seq['acc_num'];

            /*
             * GET Account Bank
             */
            $sql = "select bank_acc_num from account_bank where acc_bank_id = $bank";
            $seq = $this->_db->fetchRow($sql);
            $accBank = $seq['bank_acc_num'];

            /*
             * GET Trans Attribut
             */
            $sql = "select trans_type_id, trans_type_desc from trans_type where trans_type_code = '2003'";
            $seq = $this->_db->fetchRow($sql);
            $transId  = $seq['trans_type_id'];
            $descr  = $seq['trans_type_desc'];

            /*
             * Insert Trans Header
             */
            $data = array(
                'trans_type_id' => $transId,
                'trans_dtm' => new Zend_Db_Expr('NOW()'),
                'user_ref'  => $memberId,
                'trans_status' => 0
            );

            $resTH = $this->_db->insert('trans_header',$data);

            if ($resTH){
                
                $idH = $this->_db->lastInsertId();


                /*
                 * Insert Trans detail
                 */
                $data = array(
                    'trans_hid' => $idH,
                    'acc_num1' => $accNum,
                    'acc_num2' => $accBank,
                    'amt1'  => $tarik,
                    'attr1' => '2003',
                    'attr2' => 'D',
                    'descr' => $descr
                );

                $resTD = $this->_db->insert('trans_detail',$data);

                if ($resTD){
                    $idD = $this->_db->lastInsertId();


                    /*
                     * GET Trans Fee Attribut
                     */
                    $sql = "select trans_type_id, trans_type_desc from trans_type where trans_type_code = '4001'";
                    $res = $this->_db->fetchRow($sql);
                    $transFeeId  = $res['trans_type_id'];
                    $descrFee = $res['trans_type_desc'];


                    /*
                     * Insert Trans Header CHARGE FEE
                     */
                    $data = array(
                        'trans_type_id' => $transFeeId,
                        'trans_dtm' => new Zend_Db_Expr('NOW()'),
                        'user_ref'  => $memberId,
                        'trans_status' => 3
                    );

                    $resTH2 = $this->_db->insert('trans_header',$data);

                    if ($resTH2){
                        $idH2 = $this->_db->lastInsertId();

                        /*
                         * Get Withdraw Charge FEE Atribut
                         */
                        $sql = "select int_value from gparams where param_name = 'WITHDRAW_CHARGE_FEE'";
                        $res = $this->_db->fetchRow($sql);
                        $jmlFee = $res['int_value'];


                        /*
                         * Insert Trans detail CHARGE FEE
                         */
                        $data = array(
                            'trans_hid' => $idH2,
                            'acc_num1' => $accNum,
                            'amt1'  => $jmlFee,
                            'attr1' => '4001',
                            'attr2' => 'D',
                            'descr' => $descrFee
                        );

                        $resTD2 = $this->_db->insert('trans_detail',$data);

                        if ($resTD2){

                            /*
                             * Insert Trans Header CHARGE FEE to System
                             */
                            $data = array(
                                'trans_type_id' => $transFeeId,
                                'trans_dtm' => new Zend_Db_Expr('NOW()'),
                                'trans_status' => 3
                            );

                            $resTH3 = $this->_db->insert('trans_header',$data);

                            if ($resTH3){

                                $idH3 = $this->_db->lastInsertId();

                                $sysAccNum = 'SYS00003';


                                /*
                                 * Insert Trans detail CHARGE FEE to System
                                 */
                                $data = array(
                                    'trans_hid' => $idH3,
                                    'acc_num1' => $sysAccNum,
                                    'amt1'  => $jmlFee,
                                    'attr1' => '4001',
                                    'attr2' => 'K',
                                    'descr' => $descrFee
                                );

                                $resTD3 = $this->_db->insert('trans_detail',$data);

                                if ($resTD3){

                                     /*
                                     * Update Accounts member
                                     */
                                    $sql = "update accounts set balance = balance-($tarik+$jmlFee) where acc_id = $accId";
                                    $resAC = $this->_db->query($sql);

                                    if ($resAC){

                                        /*
                                         * Update Accounts System
                                         */
                                        $sql = "update accounts set balance = balance+$jmlFee where acc_num = '$sysAccNum'";
                                        $resAS = $this->_db->query($sql);

                                        if ($resAS){

                                            $this->_db->commit();


                                            $result = array(
                                                'code'  => 0,
                                                'msg'   => 'Penarikan Sukses',
                                                'trans_hid'  => $idH
                                            );


                                            return $result;
                                        }
                                        
                                    }

                                }

                            }
                        }

                    }
                }
                
            }

        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $result = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'trans_hid'  => ""
            );

            return $result;
        }

    }



    function transfer($memberId, $fromAccount, $toAccount, $jmlTransfer, $keterangan){

        $this->_db->beginTransaction();
        
        $result = array();

        try {
            /*
             * GET Account Pengirim
             */
            $sql = "select acc_num, acc_name, id_reff1 from accounts where acc_id = $fromAccount";
            $seq = $this->_db->fetchRow($sql);
            $fromAccNum = $seq['acc_num'];
            $namaPengirim = $seq['acc_name'];
            $memberPengirim = $seq['id_reff1'];


            /*
             * GET Member Penerima
             */
            $sql = "select acc_name, id_reff1 from accounts where acc_num = '$toAccount'";
            $seq = $this->_db->fetchRow($sql);
            $namaPenerima = $seq['acc_name'];
            $memberPenerima = $seq['id_reff1'];

            /*
             * GET Trans Attribut
             */
            $sql = "select trans_type_id, trans_type_desc from trans_type where trans_type_code = '2004'";
            $seq = $this->_db->fetchRow($sql);
            $transId  = $seq['trans_type_id'];
            $descr  = $seq['trans_type_desc']." dari $namaPengirim ke $namaPenerima";

            /*
             * Insert Trans Header dari A ke B
             */
            $data = array(
                'trans_type_id' => $transId,
                'trans_dtm' => new Zend_Db_Expr('NOW()'),
                'user_ref'  => $memberId,
                'trans_status' => 3
            );

            $resTH = $this->_db->insert('trans_header',$data);

            if ($resTH){
                $idH = $this->_db->lastInsertId();

                /*
                 * Insert Trans detail dari A ke B
                 */
                $data = array(
                    'trans_hid' => $idH,
                    'acc_num1' => $fromAccNum,
                    'acc_num2' => $toAccount,
                    'amt1'  => $jmlTransfer,
                    'attr1' => '2004',
                    'attr2' => 'D', // untuk penyimpanan isi dgn Debit (D)
                    'attr3' => $keterangan,
                    'descr' => $descr
                );

                $resTD = $this->_db->insert('trans_detail',$data);

                if ($resTD){
                    $idD = $this->_db->lastInsertId();


                    /*
                     * Insert Trans Header dari B ke A
                     */
                    $data = array(
                        'trans_type_id' => $transId,
                        'trans_dtm' => new Zend_Db_Expr('NOW()'),
                        'user_ref'  => $memberPenerima,
                        'trans_status' => 3
                    );

                    $resTH2 = $this->_db->insert('trans_header',$data);

                    if ($resTH2){
                        $idH2 = $this->_db->lastInsertId();

                        /*
                         * Insert Trans detail dari B ke A
                         */
                        $data = array(
                            'trans_hid' => $idH2,
                            'acc_num1' => $toAccount,
                            'acc_num2' => $fromAccNum,
                            'amt1'  => $jmlTransfer,
                            'attr1' => '2004',
                            'attr2' => 'K', // untuk penyimpanan isi dgn Kredit (K)
                            'attr3' => $keterangan,
                            'descr' => $descr
                        );

                        $resTD2 = $this->_db->insert('trans_detail',$data);

                        if ($resTD2){

                            /*
                             * GET Trans Fee Attribut
                             */
                            $sql = "select trans_type_id, trans_type_desc from trans_type where trans_type_code = '4001'";
                            $res = $this->_db->fetchRow($sql);
                            $transFeeId  = $res['trans_type_id'];
                            $descrFee = $res['trans_type_desc'];


                            /*
                             * Insert Trans Header CHARGE FEE
                             */
                            $data = array(
                                'trans_type_id' => $transFeeId,
                                'trans_dtm' => new Zend_Db_Expr('NOW()'),
                                'user_ref'  => $memberPengirim,
                                'trans_status' => 3
                            );

                            $resTH3 = $this->_db->insert('trans_header',$data);

                            if ($resTH3){
                                $idH3 = $this->_db->lastInsertId();

                                /*
                                 * Get Transfer Charge FEE Atribut
                                 */
                                $sql = "select int_value from gparams where param_name = 'TRANSFER_CHARGE_FEE'";
                                $res = $this->_db->fetchRow($sql);
                                $jmlFee = $res['int_value'];
                                

                                /*
                                 * Insert Trans detail CHARGE FEE
                                 */
                                $data = array(
                                    'trans_hid' => $idH3,
                                    'acc_num1' => $fromAccNum,
                                    'amt1'  => $jmlFee,
                                    'attr1' => '4001',
                                    'attr2' => 'D',
                                    'descr' => $descrFee
                                );

                                $resTD3 = $this->_db->insert('trans_detail',$data);

                                if ($resTD3){

                                    
                                     /*
                                     * Insert Trans Header CHARGE FEE to System
                                     */
                                    $data = array(
                                        'trans_type_id' => $transFeeId,
                                        'trans_dtm' => new Zend_Db_Expr('NOW()'),
                                        'trans_status' => 3
                                    );

                                    $resTH4 = $this->_db->insert('trans_header',$data);

                                    if ($resTH4){
                                        $idH4 = $this->_db->lastInsertId();

                                        //$sql = "select acc_num, acc_name, id_reff1 from accounts where acc_num = 'SYS00003'";
                                        //$seq = $this->_db->fetchRow($sql);
                                        $sysAccNum = 'SYS00003'; //$seq['acc_num'];
                                        //$sysAccName = $seq['acc_name'];
                                        

                                        /*
                                         * Insert Trans detail CHARGE FEE to System
                                         */
                                        $data = array(
                                            'trans_hid' => $idH3,
                                            'acc_num1' => $sysAccNum,
                                            'amt1'  => $jmlFee,
                                            'attr1' => '4001',
                                            'attr2' => 'K',
                                            'descr' => $descrFee
                                        );

                                        $resTD4 = $this->_db->insert('trans_detail',$data);

                                        if ($resTD4){

                                            /*
                                             * Update Accounts Pengirim
                                             */
                                            $sql = "update accounts set balance = balance-($jmlTransfer+$jmlFee) where acc_id = $fromAccount";
                                            $resUA = $this->_db->query($sql);

                                            if ($resUA){

                                                /*
                                                 * Transfer ke Accounts Penerima
                                                 */
                                                $sql = "update accounts set balance = balance+$jmlTransfer where acc_num = '$toAccount'";
                                                $resT = $this->_db->query($sql);

                                                if ($resT){

                                                    /*
                                                     * Transfer Fee ke Accounts System
                                                     */
                                                    $sql = "update accounts set balance = balance+$jmlFee where acc_num = '$sysAccNum'";
                                                    $resS = $this->_db->query($sql);

                                                    if ($resS){

                                                        //Kirim SMS Transfer
                                                        $codetransferFrom = '1002';
                                                        $codetransferTo   = '1003';

                                                        $this->insertSMS($memberPengirim, $codetransferFrom, $jmlTransfer, $namaPenerima);
                                                        $this->insertSMS($memberPenerima, $codetransferTo, $jmlTransfer, $namaPengirim);

                                                        $this->_db->commit();

                                                        $result = array(
                                                            'code'  => 0,
                                                            'msg'   => 'Transfer Sukses',
                                                            'trans_hid'  => $idH
                                                        );

                                                        return $result;
                                                    }
                                                }
                                            }

                                        }

                                    }
                                }
                            }
                        }
                    }
                }
            }
            
        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $result = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'trans_hid'  => ""
            );

            return $result;
        }
    }


    function topup($memberId, $toAccount, $topup, $jenisTopup){

        $this->_db->beginTransaction();

        $result = array();

        try {

            if ($jenisTopup == '1'){    //Transfer uang
                $transStts = 0;
                $transCode = 3001;
                $debKred = "K";
            }else{                      //Ambil dari saldo e-Money
                $transStts = 3;
                $transCode = 3008;
                $debKred = "D";
            }


            /*
             * GET Account
             */
            $sql = "select acc_num from accounts where acc_id = $toAccount";
            $seq = $this->_db->fetchRow($sql);
            $toAccNum = $seq['acc_num'];

            /*
             * GET Trans Attribut
             */
            $sql = "select trans_type_id, trans_type_desc from trans_type where trans_type_code = '$transCode'";
            $seq = $this->_db->fetchRow($sql);
            $transId  = $seq['trans_type_id'];
            $descr  = $seq['trans_type_desc'];

            /*
             * Insert Trans Header
             */

            $data = array(
                'trans_type_id' => $transId,
                'trans_dtm' => new Zend_Db_Expr('NOW()'),
                'user_ref'  => $memberId,
                'trans_status' => $transStts
            );

            $resTH = $this->_db->insert('trans_header',$data);

            if ($resTH){
                $idH = $this->_db->lastInsertId();

                /*
                 * Insert Trans detail
                 */
                $data = array(
                    'trans_hid' => $idH,
                    'acc_num1' => $toAccNum,
                    'acc_num2' => $toAccNum,
                    'amt2'  => $topup,
                    'attr1' => $transCode,
                    'attr2' => $debKred,
                    'attr3' => $jenisTopup,
                    'descr' => $descr
                );

                $resTD = $this->_db->insert('trans_detail',$data);

                if ($resTD){

                    if ($jenisTopup == "2"){    // Ambil dari saldo e-Money

                        /*
                         * GET Trans Attribut 3001
                         */
                        $sql = "select trans_type_id, trans_type_desc from trans_type where trans_type_code = '3001'";
                        $seq = $this->_db->fetchRow($sql);
                        $transId  = $seq['trans_type_id'];
                        $descr  = $seq['trans_type_desc'];

                        // Create Transaksi kedua
                        $dataTH2 = array(
                            'trans_type_id' => $transId,
                            'trans_dtm' => new Zend_Db_Expr('NOW()'),
                            'user_ref'  => $memberId,
                            'trans_status' => $transStts
                        );

                        $resTH2 = $this->_db->insert('trans_header',$dataTH2);

                        if ($resTH2){
                            $idH2 = $this->_db->lastInsertId();

                            /*
                             * Insert Trans detail
                             */
                            $dataTD2 = array(
                                'trans_hid' => $idH2,
                                'acc_num1' => $toAccNum,
                                'acc_num2' => $toAccNum,
                                'amt2'  => $topup,
                                'attr1' => '3001',
                                'attr2' => 'K', // untuk penyimpanan isi dgn Kredit (K)
                                'attr3' => $jenisTopup,
                                'descr' => $descr
                            );

                            $resTD2 = $this->_db->insert('trans_detail',$dataTD2);

                            if ($resTD2){

                                $sql = "update accounts set balance = balance-$topup, balance_rorder = balance_rorder+$topup where acc_num = '$toAccNum'";
                                $resTU = $this->_db->query($sql);

                                if ($resTU){
                                    $this->_db->commit();

                                    $result = array(
                                        'code'  => 0,
                                        'msg'   => 'Top Up berhasil',
                                        'trans_hid'  => $idH
                                    );
                                }
                            }
                        }
                    
                    }else{

                        $this->_db->commit();

                        $result = array(
                            'code'  => 0,
                            'msg'   => 'Top Up berhasil diantrikan',
                            'trans_hid'  => $idH
                        );

                        
                    }

                    return $result;
                }
            }

        }catch (Exception $e)
        {
            $this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $result = array(
                'code'  => 1,
                'msg'   => $errMsg,
                'trans_hid'  => ""
            );

            return $result;
        }
    }


    function insertSMS($memberId, $code, $rp = "", $memberCode = ""){

        //$this->_db->beginTransaction();

        $ret = array();

        try {

            $rp = ($rp == "") ? $rp : number_format($rp);

            /*
             * GET mobile_no
             */
            $sql = "select mobile_no from member_contact where member_id = $memberId";
            $seq = $this->_db->fetchRow($sql);
            $hp = $seq['mobile_no'];


            /*
             * GET Wording SMS
             */
            $sql = "select wording from sms_wording where code = $code";
            $seq = $this->_db->fetchRow($sql);
            $wording  = $seq['wording'];
            $wording = str_replace("<RP>", $rp, $wording);
            $wording = str_replace("<MEMBER_CODE>", $memberCode, $wording);


            /*
             * Insert msg outbox
             */
            $data = array(
                'notel' => $hp,
                'msg' => $wording,
                'status'  => 'F',
                'tgl_proses' => new Zend_Db_Expr('NOW()')
            );

            $res = $this->_db->insert('msg_outbox',$data);

            if ($res){
                //$this->_db->commit();

                /*
                 * GET Biaya SMS
                 */
                $sql = "select amount_per_unit, descr from bonus_type where bonus_type_code = 'A01'";
                $seq = $this->_db->fetchRow($sql);
                $biaya = $seq['amount_per_unit'];
                $descr  = $seq['descr'];

                /*
                 * Get AccNum Member
                 */
                $sql = "select acc_num from accounts where id_reff1 = $memberId";
                $seq = $this->_db->fetchRow($sql);
                $accNum = $seq['acc_num'];


                /*
                 * Insert Trans Header
                 */
                $data = array(
                    'trans_type_id' => 17,
                    'trans_dtm' => new Zend_Db_Expr('NOW()'),
                    'user_ref'  => $memberId,
                    'trans_status' => 3 // Set status ke 3 dianggap langsung sukses
                );

                $resTH = $this->_db->insert('trans_header',$data);

                if ($resTH){

                    $idH = $this->_db->lastInsertId();

                    /*
                     * Insert Trans detail
                     */

                    $data = array(
                        'trans_hid' => $idH,
                        'acc_num1' => $accNum,
                        'amt1'  => $biaya,
                        'attr1' => 'A01',
                        'attr2' => 'D', // untuk penyimpanan isi dgn Kredit (K)
                        'descr' => $descr
                    );

                    $resTD = $this->_db->insert('trans_detail',$data);

                    if ($resTD){

                        // Update Account Member
                        $sql = "update accounts set balance = balance-$biaya where id_reff1 = ".$memberId;
                        $resBal = $this->_db->query($sql);

                        if ($resBal){

                            //Trans System
                            $accNumSys = 'SYS00001';

                            /*
                             * Insert Trans Header
                             */
                            $data = array(
                                'trans_type_id' => 17,
                                'trans_dtm' => new Zend_Db_Expr('NOW()'),
                                //'user_ref'  => $memberId,
                                'trans_status' => 3 // Set status ke 3 dianggap langsung sukses
                            );

                            $resTH2 = $this->_db->insert('trans_header',$data);

                            if ($resTH2){

                                $idH2 = $this->_db->lastInsertId();

                                /*
                                 * Insert Trans detail
                                 */

                                $data = array(
                                    'trans_hid' => $idH2,
                                    'acc_num1' => $accNumSys,
                                    'amt1'  => $biaya,
                                    'attr1' => 'A01',
                                    'attr2' => 'K', // untuk penyimpanan isi dgn Kredit (K)
                                    'descr' => $descr
                                );

                                $resTD2 = $this->_db->insert('trans_detail',$data);

                                if ($resTD2){

                                    // Update Account system
                                    $sql = "update accounts set balance = balance+$biaya where acc_num = '$accNumSys'";
                                    $resBal2 = $this->_db->query($sql);

                                    if ($resBal2){

                                        $ret = array(
                                            'code'  => 0,
                                            'msg'   => 'SMS Sukses'
                                        );

                                    }

                                }

                            }
                        }
                    }
                }
           }

        }catch (Exception $e)
        {
            
            //$this->_db->rollBack();
            //my_error_handlier($e);
            $arrE = explode(":", $e);
            $arrE2 = explode(" in ", $arrE[1]);
            $errMsg = $arrE2[0];

            $ret = array(
                'code'  => 1,
                'msg'   => $errMsg
            );
        }

        return $ret;
    }
}

?>
